﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MvePublicService.RelationsOperations
{
	/*
	 * 日本SGI株式会社
	 * ヨハンソン・ヘンリク
	 * johansson@sgi.co.jp
	 * Project: MVEPublicService
	 * 
	 * Comments:
	 *      No Comments.
	 * 
	 * Change Log:
	 * ---------------------------------------------------------------------------
	 * 1/8/2009 12:03:33 PM		Johansson Henrik		Class Created.
	 * 
	 */

	/// <summary>
	/// Helper class to create Objects from Segment Relation entries in the Database.
	/// Use this class to Load Segments Relations from the Databse.
	/// </summary>
	/// <example>Relation rel = LoadRelationFromDatabase(1, sqlConnection);</example>
	/// <seealso cref="ClipOperations.Segment"/>
	/// <remarks>INSERT:Creds</remarks>
	internal static class RelationLoader
	{
		/// <summary>
		/// Loads a single Relation from the DB into a Relation object.
		/// Requires that no other commands are open on the connection used to fetch the Relation.
		/// </summary>
		/// <param name="relationId">Id of Relation to load</param>
		/// <param name="connection">Sql Connection</param>
		/// <returns>Object Representation of the loaded Relation.</returns>
		internal static Relation LoadRelationFromDatabase(int relationId, SqlConnection connection)
		{
			SqlCommand command = new SqlCommand();
			SqlDataReader reader = null;
			command.CommandTimeout = 60;

			Relation result = null;

			try
			{
				command.Connection = connection;
				command.CommandType = System.Data.CommandType.StoredProcedure;
				command.CommandText = "GetRelationById";

				command.Parameters.Add("relationId", SqlDbType.Int).Value = relationId;
				reader = command.ExecuteReader();

				if (reader.HasRows)
				{
					reader.Read();
					result = new Relation()
					{
						Id = reader.GetInt32(3),
						relation = reader.GetByte(2),
						segment2Id = reader.GetInt32(1),
						segment1Id = reader.GetInt32(0)
					};
				}
			}
			catch (NullReferenceException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = null;
			}
			catch (SqlException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = null;
			}
			catch (InvalidOperationException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = null;
			}
			catch (IndexOutOfRangeException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = null;
			}
			finally
			{
				command.Dispose();
				if (reader != null)
				{
					reader.Close();
					reader.Dispose();
				}
			}

			return result;
		}//end:LoadRelationFromDatabase

		/// <summary>
		/// Loads all related segment Ids containing the given segmentId 
		/// from the DB into an id (int) List object.
		/// Requires that no other commands are open on the connection used to fetch the Relation.
		/// </summary>
		/// <param name="segmentId">Id of Segment whose related segment ids to load</param>
		/// <param name="connection">Sql Connection</param>
		/// <returns>List Object Representation of the discovered related Segment Ids.</returns>
		internal static List<int> FindRelatedSegmentIdsInDatabase(int segmentId, SqlConnection connection)
		{
			SqlCommand command = new SqlCommand();
			SqlDataReader reader = null;
			command.CommandTimeout = 60;

			List<int> result = new List<int>();

			List<Relation> _discoveredRelations =
				new List<Relation>();

			try
			{
				command.Connection = connection;
				command.CommandType = System.Data.CommandType.StoredProcedure;
				command.CommandText = "GetRelatedSegmentsBySegmentId";

				command.Parameters.Add("segmentId", SqlDbType.Int).Value = segmentId;
				reader = command.ExecuteReader();

				if (reader.HasRows)
				{
					while (reader.Read())
					{
						_discoveredRelations.Add(new Relation()
						{
							Id = reader.GetInt32(3),
							relation = reader.GetByte(2),
							segment2Id = reader.GetInt32(1),
							segment1Id = reader.GetInt32(0)
						});
					}
				}

				//get the unique related ideas...
				foreach (Relation rel in _discoveredRelations)
				{
					if (rel.segment1Id != segmentId)
					{
						if (result.Contains(rel.segment1Id) == false)
							result.Add(rel.segment1Id);
					}

					if (rel.segment2Id != segmentId)
					{
						if (result.Contains(rel.segment2Id) == false)
							result.Add(rel.segment2Id);
					}
				}

			}
			catch (NullReferenceException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = new List<int>();
			}
			catch (SqlException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = new List<int>();
			}
			catch (InvalidOperationException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = new List<int>();
			}
			catch (IndexOutOfRangeException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = new List<int>();
			}
			finally
			{
				command.Dispose();
				if (reader != null)
				{
					reader.Close();
					reader.Dispose();
				}
			}

			return result;
		}//end:FindRelatedSegmentIdsInDatabase
	}
}
